package cn.tedu.movie.mapper;

import cn.tedu.movie.entity.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
public interface MovieMapper {
    @Select("select url from movie where id=1")
    String select();
    @Select("SELECT m.url url,m.name `name`,m.duration duration,\n" +
            "ca.name `category`,h.name `hall_name`,c.name `cinema_name`,\n" +
            "se.start_date `start_time`,se.price `price`\n" +
            "FROM `category` ca\n" +
            "LEFT join `movie` m ON ca.id=m.category_id\n" +
            "LEFT JOIN `session` se ON m.id=se.movie_id\n" +
            "LEFT JOIN `hall` h ON se.hall_id=h.id\n" +
            "LEFT JOIN `cinema` c ON h.cinema_id=c.id\n" +
            "WHERE se.id=#{id}")
    @Result(column = "hall_name",property = "hallName")
    @Result(column = "cinema_name",property = "cinemaName")
    @Result(column = "start_time",property = "startDate")
    Movie sessionMovie(Integer id);
    @Select("SELECT id movieId,`name` movieName,url movieUrl,playdate playDate" +
            ",like_people likePeople,score FROM `movie`\n" +
            "WHERE STATUS_id=#{movieStatusId} order by playdate desc")
    List<MovieByStatusId> movielistByStatusId(Integer movieStatusId);
    @Select("select * from `category`")
    List<Category> getCategory();
    @Select("select * from region")
    List<Region> getRegion();
    @Select("select * from `movie_status`")
    List<MovieStatus> getMovieStatus();
    @Select("SELECT  m.name\n" +
            "FROM `actor` a,`movie` m,`movie_actor` ma\n" +
            "WHERE a.id=ma.actor_id AND m.id=ma.movie_id AND a.id=#{id}" )
//            "order by m.score desc limit 0,3")
    List<String> getRep(Integer id);
    @Select("SELECT m.id movieId,m.url movieUrl,m.name movieName,m.score score\n" +
            ",m.playdate playDate,c.name category\n" +
            " from `movie` m left JOIN `category` c ON c.id=m.category_id \n" +
            " WHERE m.NAME like concat('%',#{name},'%')")
    List<MovieByStatusId> searchByName(String name);
    @Select(" SELECT m.id movieId,m.url movieUrl,m.name movieName,m.score score\n" +
            ",m.playdate playDate,c.name category\n" +
            " FROM `category` c\n" +
            " LEFT join `movie` m ON c.id=m.category_id\n" +
            "LEFT JOIN `movie_actor` ma ON m.id=ma.movie_id\n" +
            "LEFT JOIN `actor` a ON a.id=ma.actor_id\n" +
            "WHERE a.name like concat('%',#{name},'%')")
    List<MovieByStatusId> searchActorByActorName(String name);

//    -----------------后端-------------------
    @Select("SELECT m.id,m.name name,m.url url,r.name `region`\n" +
            ",c.name `category`,m.score_people `scorePeople`, m.score score,m.duration duration,ms.info `status`\n" +
            "FROM `movie` m,`category` c,`region` r,`movie_status` ms\n" +
            "WHERE m.category_id=c.id AND r.id=m.region_id\n" +
            "AND ms.id=m.status_id and m.name like concat('%',#{name},'%')\n"
            +"order by m.id\n"
    +"limit #{pageNum},#{limit}")
    List<Movie> movielist(Integer pageNum, Integer limit,String name);
    @Select("select count(*) from movie where name like concat('%',#{name},'%')")
    int movieCount(String name);
    @Insert("INSERT INTO `movie` VALUES(NULL,#{name},#{english},#{url},#{category}\n" +
            ",#{region},#{intro},#{duration},#{playDate},0,0,0,0,#{status})")
    int insertMovie(MovieVo movieVo);
    @Delete("delete from `movie` where id=#{id}")
    int deleteById(Integer id);
    @Select("select url from `movie` where id=#{id}")
    String selectUrlById(Integer id);
    @Select("SELECT m.id id,m.name `name`, m.url url,m.duration duration,\n" +
            "c.name category,r.name region,ms.info status\n" +
            "FROM `movie` m,category c,region r,movie_status ms\n" +
            "WHERE m.category_id=c.id AND r.id=m.region_id \n" +
            "AND m.status_id=ms.id AND m.id=#{id}")
    Movie selectOne(Integer id);
    @Update("update `movie` set status_id=#{status} where id=#{id}")
    int updateMovie(Integer status,Integer id);
    @Select("SELECT a.name name,a.id actorId\n" +
            "FROM `movie` m \n" +
            "LEFT JOIN `movie_actor` ma ON m.id=ma.movie_id\n" +
            "LEFT JOIN `actor` a ON a.id=ma.actor_id\n" +
            "WHERE m.id=#{id}")
    List<Actor> actorsByMovieId(Integer id);
    @Delete("delete from `movie_actor` where movie_id=#{movieId}\n"
    +"and actor_id=#{actorId}")
    int deletCast(Integer actorId,Integer movieId);
    @Select("SELECT id FROM `actor` WHERE NAME=#{name}")
    Integer getActorNameById(String name);
    @Insert("INSERT INTO `movie_actor` VALUES(NULL,#{movieId},#{actorId})")
    int addCast(Integer actorId, Integer movieId);
    @Select("select id from `movie` where name=#{name}")
    Integer selectByName(String name);
}
